package com.yungu.swift.admin.utils;

import com.yungu.swift.utils.DateUtils;
import com.yungu.swift.utils.ReflectUtils;
import com.yungu.swift.utils.StringUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 * Excel工具类
 *
 * @author cuixiuyin
 */
@Slf4j
public class ExcelUtil {

    /**
     * 导出
     *
     * @param response
     * @param list
     * @param clazz
     * @param templatePath
     * @throws Exception
     */
    public static void export(HttpServletResponse response, List list, Class clazz, String templatePath, String fileName,
                              Integer propertyRowNum, String dateFormat) {
        try (OutputStream os = response.getOutputStream()) {
            if (null == propertyRowNum) {
                //默认第二行为属性行
                propertyRowNum = 1;
            }
            XSSFWorkbook workbook = ExcelUtil.getExportWorkBook(list, clazz, templatePath, propertyRowNum, dateFormat);
            response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
            response.setContentType("application/octet-stream");
            response.setCharacterEncoding("utf-8");
            workbook.write(os);
        } catch (Exception e) {
            log.error("ExcelUtil export error,fileName:{}", fileName);
            e.printStackTrace();
        }
    }

    /**
     * 获取导出Excel工作薄
     *
     * @param list           数据源
     * @param clazz          类
     * @param templatePath   模板路径
     * @param propertyRowNum 属性行
     * @param dateFormat     时间格式，默认为：yyyy/MM/dd
     * @throws Exception
     */
    private static XSSFWorkbook getExportWorkBook(final List list, Class<?> clazz, String templatePath, Integer
            propertyRowNum, String dateFormat) throws Exception {
        //获取导入模板
        InputStream in = ExcelUtil.class.getResourceAsStream(templatePath);
        XSSFWorkbook wb = new XSSFWorkbook(in);
        XSSFSheet sheet = wb.getSheetAt(0);
        //设置数据格式
        XSSFDataFormat xssfDataFormat = wb.createDataFormat();
        //写入数据
        writeDate(sheet, clazz, list, propertyRowNum, dateFormat, xssfDataFormat);
        return wb;
    }

    /**
     * 写入数据
     *
     * @param sheet          表格
     * @param clazz          类
     * @param list           数据源
     * @param xssfDataFormat
     * @throws Exception
     */
    private static void writeDate(XSSFSheet sheet, Class<?> clazz, final List list, Integer propertyRowNum, String
            dateFormat, XSSFDataFormat xssfDataFormat) throws Exception {
        //获取属性行
        XSSFRow propertyRow = sheet.getRow(propertyRowNum);
        //获取属性行的列数
        int columnCount = propertyRow.getLastCellNum();
        //循环赋值
        for (int i = 0; i < list.size(); i++) {
            Row dataRow = sheet.createRow(propertyRowNum + 1 + i);
            //循环为每列赋值
            for (int j = 0; j < columnCount; j++) {
                String propertyString = propertyRow.getCell(j).getStringCellValue();
                if (StringUtils.isEmpty(propertyString)) {
                    continue;
                }
                //使用反射
                Method getMethod = clazz.getMethod("get" + upperCase(propertyString));
                if (getMethod != null) {
                    Cell cell = dataRow.createCell(j);
                    CellStyle cellStyle = propertyRow.getCell(j).getCellStyle();
                    cell.setCellStyle(cellStyle);
                    setCell(list.get(i), getMethod, cell, dateFormat, cellStyle, xssfDataFormat);
                } else {
                    dataRow.createCell(j).setCellValue("");
                }
            }
        }
        if (propertyRowNum == sheet.getLastRowNum()) {
            //没有数据，清空属性行
            sheet.removeRow(propertyRow);
        } else {
            //有数据，清空属性行，全部数据行上移一行（该函数从起始行，到结束行，上移一行）
            sheet.shiftRows(propertyRowNum + 1, sheet.getLastRowNum(), -1);
        }
    }

    /**
     * 首字母大写
     *
     * @param fieleName
     * @return
     */
    private static String upperCase(String fieleName) {
        char[] ch = fieleName.toCharArray();
        if (ch[0] >= 'a' && ch[0] <= 'z') {
            ch[0] = (char) (ch[0] - 32);
        }
        return new String(ch);
    }

    /**
     * 设置单元格的值
     *
     * @param object
     * @param method
     * @param cell
     * @param cellStyle
     * @param xssfDataFormat
     * @return
     * @throws Exception
     */
    private static Cell setCell(Object object, Method method, Cell cell, String dateFormat, CellStyle cellStyle, XSSFDataFormat xssfDataFormat) throws Exception {
        String returnType = method.getReturnType().getName();
        if (null == dateFormat) {
            dateFormat = "yyyy/MM/dd";
        }
        log.info("returnType:" + returnType);
        switch (returnType) {
            case "java.util.Date": {
                Date cellValue = (Date) method.invoke(object);
                log.info("cellValue1:" + cellValue);
                if (cellValue != null) {
                    cell.setCellValue(DateUtils.format(cellValue, dateFormat));
                }
            }
            break;
            case "java.lang.Float": {
                Float cellValue = (Float) method.invoke(object);
                log.info("cellValue2:" + cellValue);
                if (cellValue != null) {
                    cell.setCellValue(Double.valueOf(cellValue.toString()));
                    //小数格式保留两位小数
                    cellStyle.setDataFormat(xssfDataFormat.getFormat("#,#0.00"));
                }
            }
            break;
            case "java.lang.Double": {
                Double cellValue = (Double) method.invoke(object);
                log.info("cellValue3:" + cellValue);
                if (cellValue != null) {
                    cell.setCellValue(cellValue);
                    //小数格式保留两位小数
                    cellStyle.setDataFormat(xssfDataFormat.getFormat("#,#0.00"));
                }
            }
            break;
            case "java.lang.String": {
                try {
                    log.info("" + object.toString());
                    String cellValue = (String) method.invoke(object);
                    if (StringUtils.isNotEmpty(cellValue)) {
                        cell.setCellValue(cellValue);
                    }
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
            break;
            case "java.lang.Integer": {
                Integer cellValue = (Integer) method.invoke(object);
                log.info("cellValue5:" + cellValue);
                if (cellValue != null) {
                    cell.setCellValue(cellValue);
                }
            }
            break;
            case "java.math.BigDecimal": {
                java.math.BigDecimal cellValue = (java.math.BigDecimal) method.invoke(object);
                log.info("cellValue6:" + cellValue);
                if (cellValue != null) {
                    cell.setCellValue(cellValue.doubleValue());
                    //小数格式保留两位小数
                    cellStyle.setDataFormat(xssfDataFormat.getFormat("#,#0.00"));
                }
            }
            break;
            default:
                break;
        }
        return cell;
    }


    /**
     * 拼接导出文件名
     */
    public static String getFileName(String description) {
        String format = DateUtils.format(new Date(), "yyyyMMddHHmm");
        return description + format;
    }


    /**
     * 导出Excel
     *
     * @param response         响应
     * @param fileName         导出文件名
     * @param list             数据集合
     * @param columnSize       列数量
     * @param tableHeaderArray 表头数组
     * @param expressionArray  字段数组或表达式数组
     * @param columnWidthArray 列宽数组
     * @return String
     * @author cuixiuyin
     * @date 2018/10/24
     */
    public static void export(HttpServletResponse response, String fileName, final List list, int columnSize, String[]
            tableHeaderArray, String[] expressionArray, Integer[] columnWidthArray, Map<Object, Object>[] dictionaryMapArray) {
        if (columnSize != tableHeaderArray.length) {
            log.error("导出错误，列标题数量必须等于列数量, fileName:{}", fileName);
            return;
        }
        if (columnSize != expressionArray.length) {
            log.error("导出错误，表达式数量必须等于列数量, fileName:{}", fileName);
            return;
        }
        if (columnWidthArray != null && columnSize != columnWidthArray.length) {
            log.error("导出错误，列宽数量必须等于列数量, fileName:{}", fileName);
            return;
        }
        if (dictionaryMapArray != null && columnSize != dictionaryMapArray.length) {
            log.error("导出错误，字典数量必须等于列数量, fileName:{}", fileName);
            return;
        }
        XSSFWorkbook workbook = new XSSFWorkbook();
        //建立新的sheet对象（excel的表单）
        XSSFSheet sheet = workbook.createSheet(fileName);
        //设置列宽
        if (columnWidthArray != null) {
            for (int i = 0; i < columnSize; i++) {
                sheet.setColumnWidth(i, columnWidthArray[i] * 256);
            }
        } else {
            for (int i = 0; i < columnSize; i++) {
                sheet.setColumnWidth(i, 25 * 256);
            }
        }
        //设置表头
        XSSFRow thRow = sheet.createRow(0);
        for (int i = 0; i < columnSize; i++) {
            thRow.createCell(i).setCellValue(tableHeaderArray[i]);
        }
        //设置内容
        for (int i = 0; i < list.size(); i++) {
            Object record = list.get(i);
            XSSFRow tdRow = sheet.createRow(i + 1);
            for (int j = 0; j < columnSize; j++) {
                String expression = expressionArray[j];
                XSSFCell cell = tdRow.createCell(j);
                setCellValue(cell, record, expression, dictionaryMapArray[j]);
            }
        }
        try (OutputStream os = response.getOutputStream()) {
            response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            workbook.write(os);
            os.flush();
        } catch (IOException e) {
            log.error("ExcelUtil export error. fileName:{}", fileName);
        }
    }


    /**
     * 重载
     */
    public static void export(HttpServletResponse response, String fileName, List list, int columnSize, String[]
            tableHeaderArray, String[] expressionArray) {
        export(response, fileName, list, columnSize, tableHeaderArray, expressionArray, null, null);
    }

    /**
     * 重载
     */
    public static void export(HttpServletResponse response, String fileName, List list, int columnSize, String[]
            tableHeaderArray, String[] expressionArray, Integer[] columnWidthArray) {
        export(response, fileName, list, columnSize, tableHeaderArray, expressionArray, columnWidthArray, null);
    }

    /**
     * 重载
     */
    public static void export(HttpServletResponse response, String fileName, List list, int columnSize, String[]
            tableHeaderArray, String[] expressionArray, Map<Object, Object>[] dictionaryMapArray) {
        export(response, fileName, list, columnSize, tableHeaderArray, expressionArray, null, dictionaryMapArray);
    }


    private static void setCellValue(XSSFCell cell, Object record, String expression, Map<Object, Object> dictionaryMap) {
        try {
            if (expression.contains("#")) {
                String fieldValue = formatExpression(record, expression, dictionaryMap);
                cell.setCellValue(fieldValue);
            } else {
                Object fieldValue = ReflectUtils.getFieldValue(record, expression);
                //为空直接返回
                if (fieldValue == null) {
                    cell.setCellValue("");
                    return;
                }
                //若有字典转换
                if (dictionaryMap != null) {
                    fieldValue = dictionaryMap.get(fieldValue);
                }
                //转换数据类型并赋值
                if (fieldValue instanceof String) {
                    cell.setCellValue((String) fieldValue);
                } else if (fieldValue instanceof Date) {
                    cell.setCellValue(DateUtils.format((Date) fieldValue));
                } else if (fieldValue instanceof BigDecimal) {
                    cell.setCellValue(((BigDecimal) fieldValue).doubleValue());
                } else if (fieldValue instanceof Double) {
                    cell.setCellValue((Double) fieldValue);
                } else if (fieldValue instanceof Integer) {
                    cell.setCellValue(((Integer) fieldValue).doubleValue());
                } else {
                    cell.setCellValue("");
                }
            }
        } catch (ReflectiveOperationException e) {
            log.error("ExcelUtil setCellValue error.", e);
        }
    }

    private static String formatExpression(Object record, String str, Map<Object, Object> dictionaryMap) throws ReflectiveOperationException {
        while (str.contains("#")) {
            int start = str.indexOf("#");
            int end = str.indexOf("#", start + 1);
            if (end == -1) {
                return "expression illegal";
            }
            String exp = str.substring(start, end + 1);
            String fieldName = exp.substring(1, exp.length() - 1);
            String fieldValueStr = formatValueStr(record, fieldName, dictionaryMap);
            str = str.replaceAll(exp, fieldValueStr);
        }
        return str;
    }

    private static String formatValueStr(Object record, String fieldName, Map<Object, Object> dictionaryMap) throws ReflectiveOperationException {
        Object fieldValue = ReflectUtils.getFieldValue(record, fieldName);
        if (fieldValue == null) {
            return "";
        }
        //若有字典转换
        if (dictionaryMap != null) {
            Object dictValue = dictionaryMap.get(fieldValue);
            if (dictValue != null) {
                fieldValue = dictValue;
            }
        }
        //转换数据类型
        if (fieldValue instanceof String) {
            return (String) fieldValue;
        } else if (fieldValue instanceof Date) {
            return DateUtils.format((Date) fieldValue);
        } else if (fieldValue instanceof BigDecimal) {
            return fieldValue.toString();
        } else {
            return String.valueOf(fieldValue);
        }
    }

    /**
     * @param srcfile 文件名数组
     * @param zipfile 压缩后文件
     */
    public static void zipFiles(File[] srcfile, File zipfile) {
        byte[] buf = new byte[1024];
        try (ZipOutputStream out = new ZipOutputStream(new FileOutputStream(zipfile))) {
            for (int i = 0; i < srcfile.length; i++) {
                FileInputStream in = new FileInputStream(srcfile[i]);
                out.putNextEntry(new ZipEntry(srcfile[i].getName()));
                int len;
                while ((len = in.read(buf)) > 0) {
                    out.write(buf, 0, len);
                }
                out.closeEntry();
                in.close();
            }
        } catch (IOException e) {
            log.error("ExcelUtil zipFiles error.zipfile:{}", zipfile);
        }
    }
}
